﻿using System;
using System.Windows.Forms;
using System.Collections.Generic;
//using System.Data.SQLite;
using System.Data.SQLite;
using System.Data;
using System.Threading;
using System.Text;

namespace DATA_SERVER
{
    public abstract class DB_SQLite
    {
        private static string connectionString = "Data Source=" + Application.StartupPath + @"\data.s3db";
        private static int b_lock = 0;
        public readonly static object _Connlocker = new object();

        //初始化本地数据库，创建不存在的表
        public static bool Init()
        {
            try
            {
                ExecuteNonQuery("CREATE TABLE IF  NOT EXISTS [SYS_UpdateTime]([Code] VARCHAR(32) NOT NULL,[Time] DATETIME NOT NULL)");
                ExecuteNonQuery("INSERT INTO [SYS_UpdateTime] ([Code],[Time]) SELECT 'Staff_Update',DATETIME('1900-01-01 00:00:00') WHERE NOT EXISTS (SELECT * FROM [SYS_UpdateTime] WHERE [Code] = 'Staff_Update')");
                ExecuteNonQuery("CREATE TABLE IF NOT EXISTS [T_PP_AccessControl]([StaffNo] VARCHAR(20),[Time] DATETIME,[BeaMac] VARCHAR(32))");
                ExecuteNonQuery("CREATE TABLE IF NOT EXISTS [T_JC_Beacon]([BeaMac] VARCHAR(40) PRIMARY KEY,[BeaconName] VARCHAR(40),[BeaType] VARCHAR(10),[Line] VARCHAR(100))");
                ExecuteNonQuery("CREATE TABLE IF NOT EXISTS [T_SM_Staff]([Pin] VARCHAR(20) PRIMARY KEY,[StaffNo] VARCHAR(40),[TagMac] VARCHAR(40),[StaffName] VARCHAR(40),[DeptName] VARCHAR(50),[TypeJobName] VARCHAR(40),[PositionsName] VARCHAR(40),[StaffPhotoPath] VARCHAR(200))");
                ExecuteNonQuery("CREATE TABLE IF NOT EXISTS [T_SM_StaffExtra]([Pin] VARCHAR(20) PRIMARY KEY,[Type] VARCHAR(10),[Extra] TEXT)");
                return true;
            }
            catch/*(Exception ex)*/
            {
                //throw new Exception("{0}", ex);      
                return false;
            }
        }

        //获取连接，加锁保证单连接
        private static SQLiteConnection GetConnection()
        {
            lock (_Connlocker)
            {
                SQLiteConnection conn = new SQLiteConnection(connectionString);
                return conn;
            }
        }

        //填充SQLiteCommand
        private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, string cmdText)
        {
            try
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }

                cmd.Connection = conn;
                cmd.CommandText = cmdText;
            }
            catch
            {
                throw;
            }
        }

        //执行数据库命令，并返回单个int值
        public static int ExecuteNonQuery(string cmdText)
        {
            using (SQLiteCommand cmd = new SQLiteCommand())
            {
                using (SQLiteConnection conn = GetConnection())
                {
                    PrepareCommand(cmd, conn, cmdText);
                    int val = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    return val;
                }
            }
        }

        //执行数据库命令，并返回reader
        public static SQLiteDataReader ExecuteReader(string cmdText)
        {
            using (SQLiteCommand cmd = new SQLiteCommand())
            {
                SQLiteConnection conn = GetConnection();

                try
                {
                    PrepareCommand(cmd, conn, cmdText);
                    SQLiteDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    cmd.Parameters.Clear();
                    return rdr;
                }
                catch
                {
                    conn.Dispose();
                    throw;
                }
            }
        }

        //执行数据库命令，并返回单个object
        public static object ExecuteScalar(string cmdText)
        {
            using (SQLiteCommand cmd = new SQLiteCommand())
            {
                using (SQLiteConnection conn = GetConnection())
                {
                    PrepareCommand(cmd, conn, cmdText);
                    object val = cmd.ExecuteScalar();
                    cmd.Parameters.Clear();
                    return val;
                }
            }
        }

        //执行数据库命令，并返回DataTable
        public static DataTable ExecuteDataTable(string cmdText)
        {
            using (SQLiteCommand cmd = new SQLiteCommand())
            {
                using (SQLiteConnection conn = GetConnection())
                {
                    PrepareCommand(cmd, conn, cmdText);

                    using (SQLiteDataAdapter thisAdapter = new SQLiteDataAdapter(cmd))
                    {
                        DataTable dt = new DataTable();

                        try
                        {
                            thisAdapter.Fill(dt);
                            thisAdapter.Dispose();
                        }
                        catch { }
                        finally
                        {
                            if (conn.State == ConnectionState.Open)
                            {
                                conn.Dispose();
                            }
                        }

                        return dt;
                    }
                }
            }
        }

        //更新门禁机信息  将远程服务器端门禁机信息下载到本地数据库
        public static void UpdateBeacon()
        {
            /*------------------------------------下载门禁机信息-----------------------------------------*/
            try
            {
                int count = 0;
                DataTable dt = DB_SQLServer.ExecuteDataTable("SELECT [BeaMac],[BeaconName],[BeaType],[Line] FROM [T_JC_Beacon] WHERE [BeaType] = 'JZWZLY004' OR [BeaType] = 'JZWZLY005'");//[BeaType] = 'JZWZLY004' 入井门禁   [BeaType] = 'JZWZLY005' 出井门禁
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    try
                    {
                        count += ExecuteNonQuery(string.Format("REPLACE INTO [T_JC_Beacon] ([BeaMac],[BeaconName],[BeaType],[Line]) VALUES('{0}','{1}','{2}','{3}')", dt.Rows[i][0], dt.Rows[i][1], dt.Rows[i][2], dt.Rows[i][3]));
                    }
                    catch { }
                }
                if (count > 0)
                {
                    Data_Func.ShowText("门禁机信息已更新！");
                }
            }
            catch
            {
                if (DB_SQLServer.Status > 0)
                {
                    Data_Func.ShowText("门禁机信息更新失败！");
                }
                else
                {
                    Data_Func.ShowText("远程数据库连接失败！");
                }
            }
            /*-------------------------------------------------------------------------------------------*/
        }

        //本地数据上传
        public static void UpdateDB(object obj)
        {
            if (Interlocked.Exchange(ref b_lock, 1) == 0)       //互斥锁，保证同时只有一个线程
            {
                Service myService = obj as Service;

                if (!DB_SQLServer.b_Init)
                {
                    if (DB_SQLServer.Init())
                    {
                        Data_Func.ShowText("远程数据库初始化成功！");
                    }
                    else
                    {
                        Data_Func.ShowText("远程数据库初始化失败！");
                        Interlocked.Exchange(ref b_lock, 0);
                        return;
                    }
                }
                

                DateTime Lasttime, Newtime;
                /*-------------------------------------下载用户信息------------------------------------------*/
                try
                {
                    int count = 0;
                    Lasttime = Convert.ToDateTime(ExecuteScalar("SELECT [Time] FROM [SYS_UpdateTime] WHERE [Code] = 'Staff_Update'"));
                    Newtime = Convert.ToDateTime(DB_SQLServer.ExecuteScalar("SELECT GETDATE()"));
                    //DataTable dt = DB_SQLServer.ExecuteDataTable(string.Format("SELECT s.[StaffNo],t.[TagMac],s.[StaffName],(SELECT [DEPTNAME] FROM [SYS_DEPT] WHERE [SYS_DEPT].[DEPTID] = s.[DeptID]),(SELECT [TYPEJOBNAME] FROM [T_SM_TypeJob] WHERE [T_SM_TypeJob].[TYPEJOBID] = s.[TYPEJOB]),(SELECT [PositionsName] FROM [T_SM_Position] WHERE [T_SM_Position].[PositionsID] = s.[PositionsID]),s.[StaffPhotoPath],t.[NFC],e.[Type],e.[Extra],s.[STATUS] FROM ([T_SM_Staff] s LEFT JOIN [T_JC_Tag] t ON t.[StafffID] = s.[StaffID]) LEFT JOIN [T_SM_StaffExtra] e ON t.[TagMac] = e.[Pin] WHERE s.[CreateDate] > '{0}' OR s.[ModifyDate] > '{0}' OR t.[CreateDate] > '{0}' OR t.[ModifyDate] > '{0}' OR e.[ModifyTime] > '{0}'", Lasttime.AddSeconds(-30).ToString()));
                    DataTable dt = DB_SQLServer.ExecuteDataTable(string.Format("SELECT s.[StaffNo],t.[TagMac],s.[StaffName],(SELECT [DEPTNAME] FROM [SYS_DEPT] WHERE [SYS_DEPT].[DEPTID] = s.[DeptID]),(SELECT [TYPEJOBNAME] FROM [T_SM_TypeJob] WHERE [T_SM_TypeJob].[TYPEJOBID] = s.[TYPEJOB]),(SELECT [PositionsName] FROM [T_SM_Position] WHERE [T_SM_Position].[PositionsID] = s.[PositionsID]),s.[StaffPhotoPath],t.[NFC],e.[Type],e.[Extra],s.[STATUS],e.[newpin] ,e.[Pin] FROM ([T_SM_Staff] s LEFT JOIN [T_JC_Tag] t ON t.[StafffID] = s.[StaffID]) LEFT JOIN [T_SM_StaffExtra] e ON t.[TagMac] = e.[Pin] WHERE s.[CreateDate] > '{0}' OR s.[ModifyDate] > '{0}' OR t.[CreateDate] > '{0}' OR t.[ModifyDate] > '{0}' OR e.[ModifyTime] > '{0}'", Lasttime.AddSeconds(-30).ToString()));


             


                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        DataTable pin = DB_SQLServer.ExecuteDataTable(string.Format("select pin,newpin FROM [T_SM_StaffExtra] WHERE [newpin] = '{0}'", dt.Rows[i][1]));
                        if (pin.Rows.Count>0) {
                        }
                        try
                        {
                            if (dt.Rows[i][10].ToString() == "YGZT001")     //YGZT001 表示在职
                            {
                                if (Global.UseTag && (dt.Rows[i][1].ToString() == ""))
                                {//允许使用标识卡 但 无标识卡Mac
                                    continue;
                                }
                                if ((pin.Rows.Count > 0) && (pin.Rows[0][1].ToString() != "1") && (pin.Rows[0][1].ToString() != ""))
                                {//员工扩展表中有重复的newpin 删除操作(数据库和门禁机)   更新员工表操作                            
                                    myService.DelUser(pin.Rows[0][0].ToString());
                                    ExecuteNonQuery(string.Format("DELETE FROM T_SM_Staff WHERE tagmac ='{0}'", pin.Rows[0][0]));
                                    ExecuteNonQuery(string.Format("REPLACE INTO [T_SM_Staff] ([Pin],[StaffNo],[TagMac],[StaffName],[DeptName],[TypeJobName],[PositionsName],[StaffPhotoPath]) VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')", Global.UseTag ? dt.Rows[i][1] : dt.Rows[i][0], dt.Rows[i][0], dt.Rows[i][1], dt.Rows[i][2], dt.Rows[i][3], dt.Rows[i][4], dt.Rows[i][5], dt.Rows[i][6]));
                                    continue;                                    
                                }
                                
                                if (Global.AutoSync)
                                {//门禁机添加使用人员信息:员工编号/标识卡Mac,[标识卡NFC],[员工扩展Type],[员工扩展Extra],[员工在职状态],[员工扩展newpin]
                                    myService.AddUser(Global.UseTag ? dt.Rows[i][1].ToString() : dt.Rows[i][0].ToString(), dt.Rows[i][2].ToString(), dt.Rows[i][7].ToString(), dt.Rows[i][8].ToString(), dt.Rows[i][9].ToString());
                                }
                                count += ExecuteNonQuery(string.Format("REPLACE INTO [T_SM_Staff] ([Pin],[StaffNo],[TagMac],[StaffName],[DeptName],[TypeJobName],[PositionsName],[StaffPhotoPath]) VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')", Global.UseTag ? dt.Rows[i][1] : dt.Rows[i][0], dt.Rows[i][0], dt.Rows[i][1], dt.Rows[i][2], dt.Rows[i][3], dt.Rows[i][4], dt.Rows[i][5], dt.Rows[i][6]));
                            }
                            else
                            {//员工不在职
                                if (Global.AutoSync)
                                {//删除操作(门禁机)员工信息
                                    myService.DelUser(Global.UseTag ? dt.Rows[i][1].ToString() : dt.Rows[i][0].ToString());
                                }
                                //员工表员工信息  删除操作(数据库)
                                ExecuteNonQuery(string.Format("DELETE FROM [T_SM_Staff] WHERE [Pin] = '{0}'", Global.UseTag ? dt.Rows[i][1] : dt.Rows[i][0]));
                            }
                        }
                        catch { }
                    }
                    //在更新表 里更新 字段[员工更新]的时间
                    ExecuteNonQuery(string.Format("UPDATE [SYS_UpdateTime] SET [Time] = DATETIME('{0}') WHERE [Code] = 'Staff_Update'", Newtime.ToString("s")));
                    if (count > 0)
                    {
                        Data_Func.ShowText("（自动）用户信息已从二维系统同步到设备数据库" + count + "条！");
                    }
                }
                catch
                {
                    if (DB_SQLServer.Status > 0)
                    {
                        Data_Func.ShowText("用户信息同步失败！");
                    }
                    else
                    {
                        Data_Func.ShowText("远程数据库连接失败！");
                        Interlocked.Exchange(ref b_lock, 0);
                        return;
                    }
                }
                /*-------------------------------------------------------------------------------------------*/

                /*-------------------------------------上传新用户信息----------------------------------------*/
                try
                {
                    int count = 0;
                    DataTable dt = ExecuteDataTable("SELECT [ROWID],[Pin],[Type],[Extra] FROM [T_SM_StaffExtra] ORDER BY [ROWID] ASC");
                    //DataTable dt1 = ExecuteDataTable("SELECT t.[Pin],t.[Type],t.[Extra],tja.StafffID FROM [T_SM_StaffExtra] t left join t_jc_tag tja on tja.tagmac = t.pin  ORDER BY [Pin] ASC");
                    
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {//将[员工扩展]表 按照本地数据库 更新远程数据库
                        try
                        {
                            //Data_Func.ShowText(dt.Rows[1][1].ToString());
                            //long staffid = ExecuteNonQuery(string.Format("select StafffID from t_jc_tag where TagMac = '{0}'", dt.Rows[i][1].ToString()));
                            //通过[员工扩展]表的[Pin]字段 查[t_jc_tag]标识卡表[员工ID] 条件 标识卡Mac字段[TagMac]=[员工扩展]表的[Pin]字段
                            //long staffid = DB_SQLServer.ExecuteNonQuery(string.Format("select StafffID from t_jc_tag where TagMac = '{0}'", dt.Rows[i][1].ToString()));
                            DataTable dt1 = DB_SQLServer.ExecuteDataTable(string.Format("select StafffID from t_jc_tag where TagMac = '{0}'", dt.Rows[i][1].ToString()));
                            if (dt1.Rows.Count == 1)
                            {
                                //Data_Func.ShowText(dt1.Rows[0][0].ToString());
                                //[员工扩展]表 有则更新 无则插入
                                count += DB_SQLServer.ExecuteNonQuery(string.Format("IF NOT EXISTS (SELECT * FROM [T_SM_StaffExtra] WHERE [Pin] = '{0}') INSERT INTO [T_SM_StaffExtra] ([Pin],[Type],[Extra],[ModifyTime],[StaffID]) VALUES('{0}','{1}','{2}',GETDATE(),'{3}') ELSE UPDATE [T_SM_StaffExtra] SET [Type] = '{1}',[Extra] = '{2}',[ModifyTime] = GETDATE(),[StaffID] ='{3}' WHERE [Pin] = '{0}'", dt.Rows[i][1], dt.Rows[i][2], dt.Rows[i][3], dt1.Rows[0][0].ToString()));
                            }
                            else
                            {
                                for (int j = 0; j < dt1.Rows.Count; j++)
                                {
                                    //Data_Func.ShowText(dt1.Rows[j][0].ToString());
                                    count += DB_SQLServer.ExecuteNonQuery(string.Format("IF NOT EXISTS (SELECT * FROM [T_SM_StaffExtra] WHERE [Pin] = '{0}') INSERT INTO [T_SM_StaffExtra] ([Pin],[Type],[Extra],[ModifyTime],[StaffID]) VALUES('{0}','{1}','{2}',GETDATE(),'{3}') ELSE UPDATE [T_SM_StaffExtra] SET [Type] = '{1}',[Extra] = '{2}',[ModifyTime] = GETDATE(),[StaffID] ='{3}' WHERE [Pin] = '{0}'", dt.Rows[i][1], dt.Rows[i][2], dt.Rows[i][3], dt1.Rows[j][0].ToString()));
                                }
                            }                           
                            //count += DB_SQLServer.ExecuteNonQuery(string.Format("IF NOT EXISTS (SELECT * FROM [T_SM_StaffExtra] WHERE [Pin] = '{0}') INSERT INTO [T_SM_StaffExtra] ([Pin],[Type],[Extra],[ModifyTime]) VALUES('{0}','{1}','{2}',GETDATE()) ELSE UPDATE [T_SM_StaffExtra] SET [Type] = '{1}',[Extra] = '{2}',[ModifyTime] = GETDATE() WHERE [Pin] = '{0}'", dt.Rows[i][1], dt.Rows[i][2], dt.Rows[i][3]));
                            //count += DB_SQLServer.ExecuteNonQuery(string.Format("IF NOT EXISTS (SELECT * FROM [T_SM_StaffExtra] WHERE [Pin] = '{0}') INSERT INTO [T_SM_StaffExtra] ([Pin],[Type],[Extra],[ModifyTime],[StaffID]) VALUES('{0}','{1}','{2}',GETDATE(),'{3}') ELSE UPDATE [T_SM_StaffExtra] SET [Type] = '{1}',[Extra] = '{2}',[ModifyTime] = GETDATE(),[StaffID] ='{3}' WHERE [Pin] = '{0}'", dt.Rows[i][1], dt.Rows[i][2], dt.Rows[i][3], staffid.ToString()));
                            ExecuteNonQuery(string.Format("DELETE FROM [T_SM_StaffExtra] WHERE [ROWID] = '{0}'", dt.Rows[i][0]));
                        }
                        catch/*(Exception e)*/
                        {
                            //e.GetBaseException();
                        }
                    }
                    if (count > 0)
                    {
                        Data_Func.ShowText("已上传" + count + "条新用户信息！");
                    }
                }
                catch
                {
                    if (DB_SQLServer.Status > 0)
                    {
                        Data_Func.ShowText("新用户信息上传失败！");
                    }
                    else
                    {
                        Data_Func.ShowText("远程数据库连接失败！");
                        Interlocked.Exchange(ref b_lock, 0);
                        return;
                    }
                }
                /*-------------------------------------------------------------------------------------------*/

                /*-------------------------------------上传门禁记录------------------------------------------*/
                try
                {
                    int count = 0;
                    DataTable dt = ExecuteDataTable("SELECT [ROWID],[StaffNo],[Time],[BeaMac] FROM [T_PP_AccessControl] ORDER BY [Time] ASC");
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        try
                        {
                            count += DB_SQLServer.ExecuteNonQuery(string.Format("INSERT INTO [T_PP_AccessControl] ([StaffNo],[Time],[BeaMac]) VALUES('{0}','{1}','{2}')", dt.Rows[i][1], dt.Rows[i][2], dt.Rows[i][3]));
                            ExecuteNonQuery(string.Format("DELETE FROM [T_PP_AccessControl] WHERE [ROWID] = '{0}'", dt.Rows[i][0]));
                        }
                        catch { }
                    }
                    try
                    {
                        DB_SQLServer.ExecuteNonQuery(string.Format("DELETE FROM [T_PP_AccessControl] WHERE [Time] < DATEADD(DAY,-30,GETDATE())"));
                    }
                    catch { }
                    if (count > 0)
                    {
                        Data_Func.ShowText("已上传" + count + "条门禁记录！");
                    }
                }
                catch
                {
                    if (DB_SQLServer.Status > 0)
                    {
                        Data_Func.ShowText("门禁记录上传失败！");
                    }
                    else
                    {
                        Data_Func.ShowText("远程数据库连接失败！");
                        Interlocked.Exchange(ref b_lock, 0);
                        return;
                    }
                }
                /*-------------------------------------------------------------------------------------------*/

                Interlocked.Exchange(ref b_lock, 0);
            }
        }
    }
}
